﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;

using OfficeOpenXml;



namespace ESMaster
{
    class ExcelHelper
    {
        public static bool Export03(EngineeringSample es, string filename)
        {

            if (!File.Exists(filename))
            {
                return false;
            }
            try
            {
                IWorkbook wk;
                using (FileStream file = new FileStream(filename, FileMode.Open, FileAccess.Read))
                {
                    wk = WorkbookFactory.Create(file);
                }
                ISheet sheet = wk.GetSheetAt(0);
                if (sheet == null) return false;

                int rowNum = sheet.LastRowNum;

                IRow newRow = sheet.CreateRow(rowNum);

                for (int i = 0; i < 8; i++)
                {
                    ICell cell = newRow.CreateCell(i);
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue("hahah" + i.ToString());
                }

                using (FileStream file = new FileStream(filename, FileMode.Create, FileAccess.Write))
                {
                    wk.Write(file);
                }

            }
            catch (Exception)
            {

                throw;
            }



            return true;
        }


        public static bool Export07(EngineeringSample es, string filename)
        {
            using (ExcelPackage excel = new ExcelPackage(new FileInfo(filename)))
            {
                ExcelWorkbook book = excel.Workbook;
                ExcelWorksheet sheet = book.Worksheets[1];
                int rowIndex = sheet.Dimension.End.Row + 1;

                sheet.Cells[rowIndex, 1].Value = es.InitialDate;
                sheet.Cells[rowIndex, 2].Value = es.ESNumber;
                sheet.Cells[rowIndex, 3].Value = es.Project;
                sheet.Cells[rowIndex, 4].Value = es.SendingSite;
                sheet.Cells[rowIndex, 5].Value = es.PartNumber;
                sheet.Cells[rowIndex, 6].Value = es.DrawingNum;
                sheet.Cells[rowIndex, 7].Value = es.PartDesp;
                sheet.Cells[rowIndex, 8].Value = es.Purpose;
                sheet.Cells[rowIndex, 9].Value = es.Originator;
                sheet.Cells[rowIndex, 10].Value = es.IssueQty;
                sheet.Cells[rowIndex, 11].Value = es.ScrapQty;
                sheet.Cells[rowIndex, 12].Value = es.IssueSendingSite;
                sheet.Cells[rowIndex, 13].Value = es.EsSampleQty;
                sheet.Cells[rowIndex, 14].Value = es.QcSampleQty;
                sheet.Cells[rowIndex, 15].Value = es.Comments;

                excel.Save();
            }

            return true;
        }


        public static bool Export(EngineeringSample es, string filename)
        {
            if (filename.IndexOf(".xlsx") > 0)
            {
                return Export07(es, filename);
            }
            else if (filename.IndexOf(".xls") > 0)
            {
                return Export03(es, filename);
            }
            else
            {
                return false;
            }
        }

    }
}
